﻿using System;
using System.Collections.Generic;
using System.Linq;
using MVCLD.Helpers;

namespace MVCLD.Models
{
    public class MVCLDServiceModels
    {

        #region MVCLD DataContext
        // Access Data context
        mvcldDataContext db = new mvcldDataContext(); 
        #endregion

        #region Categories DAL
        public Guid InsertCategory(Category Cat)
        {
            db.Categories.InsertOnSubmit(Cat);
            Save();
            return Cat.CategoryID;
        }

        public Boolean DeleteSingleCategory(Guid ID)
        {
            try
            {
                var cat = db.Categories.Single(category => category.CategoryID == ID);
                db.Categories.DeleteOnSubmit(cat);
                Save();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public Boolean DeleteMultipleCategories(List<Category> Categories)
        {
            try
            {
                db.Categories.DeleteAllOnSubmit(Categories);
                Save();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public IQueryable<Category> GetAllCategories()
        {
            return db.Categories;
        }

        public IQueryable<Category> GetAllMainCategories()
        {
            return db.Categories.Where(cat => cat.MainCategory == true);
        }

        public IQueryable<Category> GetSubCategories(int UrlID)
        {
            var cat = db.Categories.SingleOrDefault(c => c.UrlID == UrlID.SQLInject());
            return from p in db.Categories
                       where p.ParentCategoryID == cat.CategoryID
                       orderby p.CategoryName
                       select p;
        }

        public string GetCategoryName(int UrlID)
        {
            return db.Categories.SingleOrDefault(c => c.UrlID == UrlID.SQLInject()).CategoryName;
        }

        public int CountMainCategories()
        {
            return db.Categories.Where(c => c.MainCategory == true).Count();
        }

        public int CountSubCategories()
        {
            return db.Categories.Where(c => c.ParentCategoryID != null).Count();
        }

        public Category GetSingleCategory(Guid id)         
        {
            return db.Categories.SingleOrDefault(m => m.CategoryID == id);
        }

        #endregion

        #region Article DAL
        /// <summary>
        /// Create a new article by passing an Article object
        /// </summary>
        /// <param name="TheArticle">Article Object</param>
        /// <returns>The GUID of the newly created article</returns>
        public Guid InsertArticle(Article TheArticle)
        {
            TheArticle.ArticleContent = TheArticle.ArticleContent.SQLInject();
            TheArticle.ArticleSnippet = TheArticle.ArticleSnippet.SQLInject();
            TheArticle.ArticleTitle = TheArticle.ArticleTitle.SQLInject();
            TheArticle.Email = TheArticle.Email.SQLInject();
            TheArticle.Resource = TheArticle.Resource.SQLInject();
            TheArticle.SubmitterName = TheArticle.SubmitterName.SQLInject();
            db.Articles.InsertOnSubmit(TheArticle);
            Save();
            return TheArticle.ArticleID;
        }

        /// <summary>
        /// Delete a single article by passing in the GUID
        /// </summary>
        /// <param name="ID">GUID</param>
        /// <returns>True/False</returns>
        public Boolean DeleteSingleArticle(Guid ID)
        {
            try
            {
                var Art = db.Articles.SingleOrDefault(a => a.ArticleID == ID);
                db.Articles.DeleteOnSubmit(Art);
                Save();
                return true;
            }
            catch
            {
                return false;
            }
        }
        
        /// <summary>
        /// Delete a single article by passing in the article title
        /// </summary>
        /// <param name="Title">The article title</param>
        /// <returns>True/False</returns>
        public Boolean DeleteSingleArticle(string Title)
        {
            try
            {
                var Art = db.Articles.Single(a => a.ArticleTitle == Title.SQLInject());
                db.Articles.DeleteOnSubmit(Art);
                Save();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public Boolean DeleteMultipleArticles(List<Article> Articles)
        {
            try
            {
                db.Articles.DeleteAllOnSubmit(Articles);
                Save();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public IQueryable<Article> GetArticles()
        {
            return db.Articles.OrderBy(a => a.ArticleTitle);
        }

        public IQueryable<Article> GetArticles(Boolean IsLive)
        {
            return db.Articles.Where(a => a.IsLive == IsLive).OrderBy(a => a.ArticleTitle);
        }

        public Article GetSingleArticle(Guid ID)
        {
            return db.Articles.SingleOrDefault(a => a.ArticleID == ID);
        }

        /// <summary>
        /// Grab a single article object by the URL ID
        /// </summary>
        /// <param name="ID">URL ID</param>
        /// <returns>Article Object</returns>
        public Article GetSingleArticle(int ID)
        {
            return db.Articles.SingleOrDefault(a => a.UrlID == ID.SQLInject());
        }

        /// <summary>
        /// Grab a single article object by the Article title
        /// </summary>
        /// <param name="ArticleTitle">The article title</param>
        /// <returns>Article Object</returns>
        public Article GetSingleArticle(string ArticleTitle)
        {
            return db.Articles.SingleOrDefault(a => a.ArticleTitle == ArticleTitle.SQLInject());
        }

        public IQueryable<Article> GetLatestSubmittedUnconfirmedArticles()
        {
            return from arts in db.Articles
                   where arts.IsLive == false
                   orderby arts.SubmissionDate descending
                   select arts;
        }

        public IQueryable<Article> GetLatestSubmittedArticlesByAmount(int HowMany)
        {
            var articles = from arts in db.Articles
                           where arts.IsLive == true
                           orderby arts.SubmissionDate descending
                           select arts;
            return articles.Take(HowMany.SQLInject());
        }

        public IQueryable<Article> GetLiveArticlesByCategoryID(Guid CategoryID)
        {
            return db.Articles.Where(a => a.CategoryID == CategoryID && a.IsLive == true);
        }

        public IQueryable<Article> GetLiveArticlesByCategoryUrlID(int UrlID)
        {
            var Cat = db.Categories.SingleOrDefault(c => c.UrlID == UrlID);
            var arts = from a in db.Articles
                        where a.CategoryID == Cat.CategoryID && a.IsLive == true
                        orderby a.ArticleTitle
                        select a;
            return arts;
        }

        public int CountArticlesByLiveOrPending(bool IsLive)
        {
            return db.Articles.Where(a => a.IsLive == IsLive).Count();
        }
        #endregion

        #region Article Types DAL
        public Guid InsertArticleType(ArticleType AType)
        {
            db.ArticleTypes.InsertOnSubmit(AType);
            Save();
            return AType.ArticleTypeID;
        }

        public ArticleType GetSingleArticleType(Guid id)
        {
            return db.ArticleTypes.SingleOrDefault(a => a.ArticleTypeID == id);
        }

        public Boolean DeleteSingleArticleType(Guid ID)
        {
            try
            {
                var AType = db.ArticleTypes.SingleOrDefault(a => a.ArticleTypeID == ID);
                db.ArticleTypes.DeleteOnSubmit(AType);
                Save();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public Boolean DeleteMultipleLinkTypes(List<ArticleType> ATypes)
        {
            try
            {
                db.ArticleTypes.DeleteAllOnSubmit(ATypes);
                Save();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public IQueryable<ArticleType> GetAllArticleTypes()
        {
            return db.ArticleTypes.OrderBy(a => a.ArticleTypeName);
        }

        #endregion

        #region General
        public void Save() 
        {
            db.SubmitChanges();
        }
        #endregion        

    }
}
